﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ANZ_AIM_DAL;
using System.Globalization;
using ANZ_AIM_BLL.Model;
using ANZ_AIM_BLL.Utils;
using ANZ_AIM_FRAMEWORK;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using System.Diagnostics;


namespace ANZ_AIM_BLL
{
    public class SaleInfoBLL
    {
        private const string TABLE_NAME = "SaleInfo";
        private string ConnectionString;
        private Dictionary<string, string> ObjectPropDict;

        public SaleInfoBLL()
        {
            ConnectionString = DBHelper.GetMainMdbConnectionString();
            this.ObjectPropDict = BuildObjectPropertyMapping();
        }

        public void Save(List<SaleInfo> saleInfoList)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                string command = string.Empty;
                foreach (SaleInfo saleInfo in saleInfoList)
                {
                    command = CommandHelper.BuildInsertCommand(saleInfo, dict, out parameters);
                    OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
                }
            }
            catch (Exception ex) { throw; }
        }
        public void UpdateProfileIDSaleCode(SaleInfo saleInfo)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                dict.Remove("ID");
                OleDbParameter[] parameters;
                string command = string.Empty;

                var conditionDict = new Dictionary<string, string>
                    {
                        {"ID", "ID"}
                    };
                command = CommandHelper.BuildUpdateCommand(saleInfo, dict, conditionDict, out parameters);
                int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);

            }
            catch (Exception ex) { throw; }
        }
        public bool Update(DataTable dtChange, List<string> profileIDList, List<string> batchFileList)
        {
            bool flag = false;
            try
            {
                OleDbParameter[] parameters;
                string command = "";
                //update by profileID
                Dictionary<string, string> channel_Change = new Dictionary<string, string>();
                Dictionary<string, string> teamName_Change = new Dictionary<string, string>();
                foreach (string profileID in profileIDList)
                {
                    SaleInfo saleInfo = new SaleInfo() { ProfileID = profileID };
                    //saleInfo.ProfileID = profileID;
                    var dict = new Dictionary<string, string>();
                    var condition = new Dictionary<string, string>();
                    DataRow[] foundRows = dtChange.Select("[ProfileID] = '" + profileID + "'");

                    foreach (DataRow row in foundRows)
                    {
                        string fieldChange = row["FieldChange"].ToString();
                        string newValue = row["NewValue"].ToString();

                        dict.Add(fieldChange, fieldChange);
                        Regex rgx = new Regex(@"^([0]?[1-9]|[1|2][0-9]|[3][0|1])[./-]([0]?[1-9]|[1][0-2])[./-]([0-9]{4}|[0-9]{2})$");
                        if (rgx.IsMatch(newValue))
                        {
                            saleInfo.GetType().GetProperty(fieldChange).SetValue(saleInfo, MiscHelper.StringToDate(newValue), null);
                        }
                        else
                        {
                            saleInfo.GetType().GetProperty(fieldChange).SetValue(saleInfo, newValue, null);
                        }

                        if (fieldChange.Equals("Channel"))
                        {
                            channel_Change.Add(profileID, newValue);
                        }
                        if (fieldChange.Equals("TeamName"))
                        {
                            teamName_Change.Add(profileID, newValue);
                        }
                    }

                    //Update saleCode
                    foreach (var key in channel_Change.Keys)
                    {
                        UpdateSaleCode(key.ToString(), channel_Change[key], "");
                        string cmd = string.Format("Update [{0}] s Set s.Channel = '" + channel_Change[key] + "' WHERE s.ProfileID = '" + key.ToString() + "'", TABLE_NAME);
                        OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, cmd);
                    }
                    foreach (var key in teamName_Change.Keys)
                    {
                        UpdateSaleCode(key.ToString(), "", teamName_Change[key]);
                    }

                    condition.Add("ProfileID", "ProfileID");
                    command = CommandHelper.BuildUpdateCommand(saleInfo, dict, condition, out parameters);
                    OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
                }

                //update by batchFile
                channel_Change = new Dictionary<string, string>();
                teamName_Change = new Dictionary<string, string>();
                command = "";
                foreach (string batchFile in batchFileList)
                {
                    SaleInfo saleInfo = new SaleInfo() { BatchFileID = batchFile };
                    //saleInfo.BatchFileID = batchFile;
                    var dict = new Dictionary<string, string>();
                    var condition = new Dictionary<string, string>();
                    DataRow[] foundRows = dtChange.Select("[BatchFile]= '" + batchFile + "'");
                    foreach (DataRow row in foundRows)
                    {
                        string fieldChange = row["FieldChange"].ToString();
                        string newValue = row["NewValue"].ToString();
                        dict.Add(fieldChange, fieldChange);
                        //Check if newValue is a date
                        Regex rgx = new Regex(@"^([0]?[1-9]|[1|2][0-9]|[3][0|1])[./-]([0]?[1-9]|[1][0-2])[./-]([0-9]{4}|[0-9]{2})$");
                        if (rgx.IsMatch(newValue))
                        {
                            saleInfo.GetType().GetProperty(fieldChange).SetValue(saleInfo, MiscHelper.StringToDate(newValue), null);
                        }
                        else
                        {
                            saleInfo.GetType().GetProperty(fieldChange).SetValue(saleInfo, newValue, null);
                        }


                        DataTable saleInforBybatchFile = GetByBatchFileID(batchFile);
                        foreach (DataRow dtRow in saleInforBybatchFile.Rows)
                        {
                            string profileID = dtRow["ProfileID"].ToString();

                            if (fieldChange.Equals("Channel"))
                            {
                                channel_Change.Add(profileID, newValue);
                            }
                            if (fieldChange.Equals("TeamName"))
                            {
                                teamName_Change.Add(profileID, newValue);
                            }
                        }

                    }
                    //Update saleCode
                    foreach (var key in channel_Change.Keys)
                    {
                        UpdateSaleCode(key.ToString(), channel_Change[key], "");
                        string cmd = string.Format("Update [{0}] s Set s.Channel = '" + channel_Change[key] + "' WHERE s.ProfileID = '" + key.ToString() + "'", TABLE_NAME);
                        OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, cmd);
                    }
                    foreach (var key in teamName_Change.Keys)
                    {
                        UpdateSaleCode(key.ToString(), "", teamName_Change[key]);
                    }

                    condition.Add("BatchFileID", "BatchFileID");
                    command = CommandHelper.BuildUpdateCommand(saleInfo, dict, condition, out parameters);
                    OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
                }
                flag = true;

            }
            catch (Exception ex) { throw; }
            return flag;
        }

        public void UpdateSaleCode(string profileID, string channel, string teamName)
        {
            try
            {
                DataTable saleInfo = GetSaleInfoByProfileId(profileID);
                string newSaleCode = "";
                if (!string.IsNullOrEmpty(teamName))
                {
                    newSaleCode = GenSaleCode(saleInfo.Rows[0]["Channel"].ToString(), teamName);
                    Console.WriteLine("Profile is " + profileID);
                }
                if (!string.IsNullOrEmpty(channel))
                {
                    string[] noTeam_Channel = new string[] {
                                "TS In-house HN 1", "TS In-house HN 2", "TS In-house HN 3",
                                "TS In-house 1", "TS In-house 2", "TS In-house 3",
                                "TS In-house 4", "TS Portfolio", "Field Sales HN", "Field Sales HCM"
                                };
                    if (noTeam_Channel.ToList<string>().Any(i => i.Equals(channel)))
                    {
                        newSaleCode = GenSaleCode(channel, "");
                    }
                    else
                    {
                        newSaleCode = GenSaleCode(channel, "TEMPTEAM"); //Temp team when editing channel
                        Console.WriteLine(newSaleCode);
                    }
                    string cmd = string.Format("Update [{0}] s Set s.TeamName = '' WHERE s.ProfileID = '" + profileID + "'", TABLE_NAME);
                    OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, cmd);

                }

                string command = string.Format("Update [{0}] s Set s.SaleCode = '" + newSaleCode + "' WHERE s.ProfileID = '" + profileID + "'", TABLE_NAME);
                OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command);

            }
            catch (Exception)
            {
                throw;
            }
        }

        public DataTable GetSaleInfoByChannel_Team(string channel, string teamName)
        {
            string command = string.Format("SELECT s.SaleCode, s.TeamName, s.Channel FROM {0} s WHERE s.Channel = '" + channel + "' AND s.TeamName = '" + teamName + "'", TABLE_NAME);
            return OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command).Tables[0];
        }

        public string GenSaleCode(string channelName, string teamName)
        {
            List<int> agentNumList = new List<int>();
            bool hasTeam = string.IsNullOrEmpty(teamName) ? false : true;
            //Get 2 or 3 first characters
            string saleCode = "";
            string teamNameLetter = !hasTeam ? "" : teamName.ElementAt(5).ToString();
            switch (channelName)
            {
                case "DS-HN 1":
                    saleCode = "D1" + teamNameLetter;
                    break;

                case "DS-HN 2":
                    saleCode = "D3" + teamNameLetter;
                    break;

                case "DS-HCM 1":
                    saleCode = "D2" + teamNameLetter;
                    break;

                case "DS-HCM 2":
                    saleCode = "D4" + teamNameLetter;
                    break;

                case "DS-HCM 3":
                    saleCode = "D6" + teamNameLetter;
                    break;

                case "TS In-house HN 1":
                    saleCode = "A1";
                    break;

                case "TS In-house HN 2":
                    saleCode = "A3";
                    break;

                case "TS In-house HN 3":
                    saleCode = "A5";
                    break;

                case "TS In-house 1":
                    saleCode = "A2";
                    break;

                case "TS In-house 2":
                    saleCode = "A4";
                    break;

                case "TS In-house 3":
                    saleCode = "A6";
                    break;

                case "TS In-house 4":
                    saleCode = "A8";
                    break;

                case "TS Portfolio":
                    saleCode = "P2";
                    break;

                case "Field Sales HN":
                    saleCode = "F1";
                    break;

                case "Field Sales HCM":
                    saleCode = "F2";
                    break;

                default:
                    break;
            }

            //Get all sale codes created with given Channel and TeamName
            Console.Write("Current channelName is {0}", channelName);
            DataTable existedSaler = GetSaleInfoByChannel_Team(channelName, teamName);

            //Create new Agentnumber
            bool forUpdate = false;
            string strNewAgentNum = "";
            int maxAgentNum = 0;
            if (existedSaler.Rows.Count > 0)
            {
                foreach (DataRow row in existedSaler.Rows)
                {
                    if (!string.IsNullOrEmpty(row["SaleCode"].ToString()))
                    {
                        string str_saleCode = row["SaleCode"].ToString();
                        Console.WriteLine("Sale code: " + str_saleCode);
                        if (!string.IsNullOrEmpty(str_saleCode))
                        {
                            forUpdate = true;
                            bool isNum = str_saleCode.ToList<char>().ElementAt(2).ToString().ToList<char>().Any(a => Char.IsNumber(a));
                            if (isNum)
                            {
                                hasTeam = false;
                                agentNumList.Add(int.Parse(str_saleCode.Substring(2, 4)));
                            }
                            else
                            {
                                agentNumList.Add(int.Parse(str_saleCode.Substring(3, 3)));
                            }
                        }
                        else
                        {
                            agentNumList.Add(maxAgentNum);
                            var check = !string.IsNullOrEmpty(teamName) ? strNewAgentNum = maxAgentNum.ToString("D3") : strNewAgentNum = maxAgentNum.ToString("D4");
                            maxAgentNum += 1;
                        }
                    }
                }

                if (forUpdate)
                {
                    for (int i = 0; i < agentNumList.Count; i++)
                    {
                        if (agentNumList.Count == 1)
                        {
                            maxAgentNum = agentNumList[0];
                            break;
                        }
                        for (int j = i + 1; j < agentNumList.Count; j++)
                        {
                            if (agentNumList[j] < agentNumList[i])
                            {
                                int temp = agentNumList[j];
                                maxAgentNum = agentNumList[i];
                                agentNumList[i] = temp;
                                agentNumList[j] = maxAgentNum;
                            }
                            else
                            {
                                maxAgentNum = agentNumList[j];
                            }
                        }
                    }
                    maxAgentNum += 1;
                    var check = hasTeam ? strNewAgentNum = maxAgentNum.ToString("D3") : strNewAgentNum = maxAgentNum.ToString("D4");
                }
            }
            else
            {
                var check = !string.IsNullOrEmpty(teamName) ? strNewAgentNum = maxAgentNum.ToString("D3") : strNewAgentNum = maxAgentNum.ToString("D4");
            }
            var checkEnd = !string.IsNullOrEmpty(teamName) ? strNewAgentNum = maxAgentNum.ToString("D3") : strNewAgentNum = maxAgentNum.ToString("D4");
            return saleCode += strNewAgentNum;
        }




        public List<SaleInfo> GetSaleInfoByBatchFile(string batchFile)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                SaleInfo saleInfo = new SaleInfo { BatchFileID = batchFile };
                var conditionDict = new Dictionary<string, string>
                    {
                        {"BatchFileID", "BatchFileID"}
                    };
                string command = CommandHelper.BuildSelectCommand(saleInfo, dict, conditionDict, out parameters);
                OleDbDataReader reader = OleDbHelper.ExecuteReader(this.ConnectionString, CommandType.Text, command, parameters);

                List<SaleInfo> saleInfoList = new List<SaleInfo>();

                while (reader.Read())
                {
                    saleInfoList.Add(PopulateObjectHelper.PopulateSaleInfoObject(reader));
                }
                reader.Close();
                return (saleInfoList.Count > 0 ? saleInfoList : null);
            }
            catch (Exception ex) { throw; }
        }

        public DataTable GetByBatchFileID(string batchFileID)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                SaleInfo saleInfo = new SaleInfo() { BatchFileID = batchFileID };
                OleDbParameter[] parameters;
                var conditionDict = new Dictionary<string, string>
                    {
                        {"BatchFileID", "BatchFileID"}
                    };
                string command = CommandHelper.BuildSelectCommand(saleInfo, dict, conditionDict, out parameters);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public DataTable GetSaleInfoByProfileId(string profileID)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                SaleInfo saleInfo = new SaleInfo() { ProfileID = profileID };
                OleDbParameter[] parameters;
                var conditionDict = new Dictionary<string, string>
                    {
                        {"ProfileID", "ProfileID"}
                    };
                string command = CommandHelper.BuildSelectCommand(saleInfo, dict, conditionDict, out parameters);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }


        public DataTable GetSaleInfo()
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                string command = string.Empty;
                command = CommandHelper.BuildSelectAllCommand(TABLE_NAME, dict);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command);
                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public DataTable GetSaleCodeMapping()
        {
            try
            {
                string command = string.Format("Select [SaleCode], [PersonalCode], [ProfileID] FROM {0}", TABLE_NAME);
                DataTable dt = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command).Tables[0];
                return dt;
            }
            catch (Exception ex) { throw; }
        }

        public DataTable Search(string saleCode, string FullName, string profileID, string batchFile)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                string command = string.Empty;
                command = CommandHelper.BuildSelectAllCommand(TABLE_NAME, dict);
                command += " WHERE (@saleCode IS NULL OR SaleCode Like @saleCode) AND (@FullName IS NULL OR EmployeeFullNameE LIKE @FullName)";
                command += "AND (@profileID IS NULL OR ProfileID=@profileID) AND (@batchFile IS NULL OR BatchFileID=@batchFile)";

                OleDbParameter[] parameters = new OleDbParameter[4];

                if (string.IsNullOrEmpty(saleCode))
                {
                    parameters[0] = new OleDbParameter("@saleCode", DBNull.Value);
                }
                else
                {
                    parameters[0] = new OleDbParameter("@saleCode", "%" + saleCode + "%");
                }

                if (string.IsNullOrEmpty(FullName))
                {
                    parameters[1] = new OleDbParameter("@FullName", DBNull.Value);
                }
                else
                {
                    parameters[1] = new OleDbParameter("@FullName", "%" + FullName + "%");
                }

                if (string.IsNullOrEmpty(profileID))
                {
                    parameters[2] = new OleDbParameter("@profileID", DBNull.Value);
                }
                else
                {

                    parameters[2] = new OleDbParameter("@profileID", profileID);
                }

                if (string.IsNullOrEmpty(batchFile))
                {
                    parameters[3] = new OleDbParameter("@batchFile", DBNull.Value);
                }
                else
                {

                    parameters[3] = new OleDbParameter("@batchFile", batchFile);
                }

                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }
        public Dictionary<string, string> BuildObjectPropertyMapping()
        {
            Dictionary<string, string> dictionary = new Dictionary<string, string>
            {
                {"ID", "ID"},
                {"CVCode", "CVCode"}, //23Jan2015
                {"ProfileID", "ProfileID"},
                {"SaleCode", "SaleCode"},

                {"EmployeeFullName", "EmployeeFullName"},
                {"EmployeeFullNameE", "EmployeeFullNameE"},
                {"Role", "Role"},
                {"Level", "Level"},
                {"Category", "Category"},
                {"ProductCategory", "ProductCategory"},
                {"Product", "Product"},
                {"TeamName", "TeamName"},
                {"TeamLeaderCode", "TeamLeaderCode"},
                {"Channel", "Channel"},
                {"JoiningDate", "JoiningDate"},
                {"PromotionTransferDate", "PromotionTransferDate"},
                {"Status", "Status"},

                {"IDCardNumber", "IDCardNumber"},
                {"DateOfBirth", "DateOfBirth"},
                {"IDIssuedDate", "IDIssuedDate"},
                {"IDIssuedPlace", "IDIssuedPlace"},
                {"PermanentAddress", "PermanentAddress"},
                {"MobilePhone", "MobilePhone"},
                {"FingerID", "FingerID"},
                {"PersonalTaxCode", "PersonalTaxCode"},

                {"ContractType", "ContractType"},
                {"ContractTerm", "ContractTerm"},
                {"ContractorName", "ContractorName"},
                {"ContractSignDate", "ContractSignDate"},
                {"ContractExpiryDate", "ContractExpiryDate"},

                {"BankAccount", "BankAccount"},
                {"BankBranchAddress", "BankBranchAddress"},
                {"BankName", "BankName"},
                {"FillingRefCode", "FillingRefCode"},
                {"ProfressiveTarget", "ProfressiveTarget"},

                {"BatchFileID", "BatchFileID"}
            };

            return dictionary;
        }


        public DataTable BuildEmploymentInformation(string batchFileID)
        {
            var dict = MiscHelper.Clone(EmploymentFieldDictionary());
            SaleInfo saleInfor = new SaleInfo() { BatchFileID = batchFileID };
            OleDbParameter[] parameters;
            var conditionDict = new Dictionary<string, string>
                    {
                        {"BatchFileID", "BatchFileID"}
                    };
            string command = CommandHelper.BuildSelectCommand(saleInfor, dict, conditionDict, out parameters);
            DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);
            return dsResult.Tables[0];
        }

        public DataTable BuildEmploymentChanged(string batchFileChangeID)
        {
            string fieldSelect = "";
            int count = 0;
            int keyCount = EmploymentFieldDictionary().Keys.Count;
            foreach (string key in EmploymentFieldDictionary().Keys)
            {
                count++;
                fieldSelect += count == keyCount ? "SaleInfo." + key : "SaleInfo." + key + ",";
            }
            string selectCmd = "SELECT " + fieldSelect +
                " FROM SaleInfo LEFT JOIN SaleChangeRequest ON SaleInfo.[ProfileID] = SaleChangeRequest.[ProfileID]" +
                " WHERE SaleChangeRequest.BatchFileChangeID = '" + batchFileChangeID + "'";
            DataSet ds = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, selectCmd);
            return ds.Tables[0];
        }


        public Dictionary<string, string> EmploymentFieldDictionary()
        {
            Dictionary<string, string> employment_FieldLabelDict = new Dictionary<string, string>();
            employment_FieldLabelDict.Add("ProfileID", "ProfileID");
            employment_FieldLabelDict.Add("SaleCode", "SaleCode");
            employment_FieldLabelDict.Add("CVCode", "CVCode");
            employment_FieldLabelDict.Add("EmployeeFullName", "EmployeeFullName");
            employment_FieldLabelDict.Add("EmployeeFullNameE", "EmployeeFullNameE");
            employment_FieldLabelDict.Add("Role", "Role");
            employment_FieldLabelDict.Add("Level", "Level");
            employment_FieldLabelDict.Add("Category", "Category");
            employment_FieldLabelDict.Add("ProductCategory", "ProductCategory");
            employment_FieldLabelDict.Add("Product", "Product");
            employment_FieldLabelDict.Add("TeamName", "TeamName");
            employment_FieldLabelDict.Add("TeamLeaderCode", "TeamLeaderCode");
            employment_FieldLabelDict.Add("Channel", "Channel");
            employment_FieldLabelDict.Add("JoiningDate", "JoiningDate");
            employment_FieldLabelDict.Add("PromotionTransferDate", "PromotionTransferDate");
            employment_FieldLabelDict.Add("Status", "Status");
            employment_FieldLabelDict.Add("ContractType", "ContractType");
            employment_FieldLabelDict.Add("ContractTerm", "ContractTerm");
            employment_FieldLabelDict.Add("ContractorName", "ContractorName");
            employment_FieldLabelDict.Add("ContractSignDate", "ContractSignDate");
            employment_FieldLabelDict.Add("ContractExpiryDate", "ContractExpiryDate");
            employment_FieldLabelDict.Add("BatchFileID", "BatchFileID");
            return employment_FieldLabelDict;
        }



    }
}
